package com.repository;

import com.data_object.TidToCountDO;
import com.util.DBUtil;
import com.util.Log;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author yuisama
 * @date 2023/03/08 17:54
 **/

public class RelationRepo {
    @SneakyThrows
    public List<TidToCountDO> selectCountByTidListGroupByTidList(List<Integer> tidList) {
        String sqlFormat = "select tid,count(*) as ref_count from relations where tid in (%s) group by tid order by tid";
        //这里给查询出来的举措索引，改名为res_count
        StringBuilder sb = new StringBuilder();
        for (Integer tid :tidList){
            sb.append(tid).append(",");
        }
        sb.delete(sb.length()-1, sb.length());
        String sql = String.format(sqlFormat,sb.toString());
        List<TidToCountDO> list = new ArrayList<>();
        try(Connection c = DBUtil.connection()){
            try(PreparedStatement ps = c.prepareStatement(sql)) {
                Log.println("执行sql:" + ps);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        TidToCountDO tidToCountDO = new TidToCountDO(
                        rs.getInt("tid"),
                        rs.getInt("ref_count")
                        );
                        list.add(tidToCountDO);
                    }
                }
            }
        }
        return list;
    }

    @SneakyThrows
    public List<Integer> selectListByAid(int aid) {
        String sql = "select tid from relations where aid = ? order by rid";
        List<Integer> tidList = new ArrayList<>();
        try(Connection c = DBUtil.connection()){
            try(PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,aid);

                Log.println("执行SQL:"+ ps);

                try(ResultSet rs = ps.executeQuery()){
                    while(rs.next()){
                        tidList.add(rs.getInt("tid"));
                    }
                }
            }
        }
        return tidList;
    }
}

